<?php 
class CartWS_Model_SalesOrderHdr extends CartWS_Model_FormulaEngine_Table
{
	const ACTIVE_ORDER = 'A';
	const CLOSE_ORDER = 'C';
	
	const INITIAL_SHIPPING_COST = 500;

	protected $_name = 'sc_sales_order_hdr';
	protected $_sequence = 'sc_sales_order_hdr_seq';
	protected $_primary = 'sales_order_id';
	protected $_dependentTables = array('Default_Model_SalesOrderLine');
	protected $_referenceMap = array(
        'ShippToAddress' => array(
            'columns'           => array( 'shipp_to_address_id' ),
            'refTableClass'     => 'Default_Model_Address',
            'refColumns'        => array( 'address_id' )
        ),
        'BillToAddress' => array(
            'columns'           => array( 'bill_to_address_id' ),
            'refTableClass'     => 'Default_Model_Address',
            'refColumns'        => array( 'address_id' )
        ),
        'PaymentMethod' => array(
            'columns'           => array( 'payment_method_id' ),
            'refTableClass'     => 'Default_Model_PaymentMethod',
            'refColumns'        => array( 'payment_method_id' )
        )
    );
 
    /**
     * 
	 * Si existe una orden para el cliente, se devuelve el id
	 * @param integer $customerId
	 * @return mixed OrderId|false
     */
    public function getActiveOrderFromCustomer( $customerId )
    {
    	$query = $this->select()
    	  	->where( 'status = ?', self::ACTIVE_ORDER )
    		->where( 'customer_id = ?', $customerId );
    		
    	$row = $this->fetchRow( $query );
    	if( !count( $row ) ){
    		return false;
    	}
		return $row;
    }

    /**
     * 
	 * Crea una orden para el customer 
	 * @param int customerid
	 * @param mixed properties
	 * @return int orderId
     */
    public function createOrder( $customerId, $properties = array())
    {
	    $row = $this->createRow();
	    $row->customer_id = $customerId;
	   	$row->status = self::ACTIVE_ORDER;
  		foreach( $properties as $prop=>$val){
  			$row->$prop = $val;
  		}
	    $row->save();
		return $row->sales_order_id;
	}

	public function getAllOrderClosedForCustomer( $customerId ) 
	{
		$rowset = $this->fetchAll( 
			$this->select()
			->where( 'status = ?', self::CLOSE_ORDER )
			->where( 'customer_id = ?', $customerId )
		);
		if( count( $rowset )) {
			return $rowset->toArray();
		}
		return false;
	}
	
   /**
     * Obtiene todas las ordenes para la sucursal correspondiente
     *
     * @param int $idBranche
     * @return mixed stdClass
     */
    public function getOrdersFromBranche( $idBranche, $query = false )
    {
        /**
         * 
         * SELECT soh.sales_order_id, soh.customer_id, soh.shipping_method, soh.order_shipping_cost, 
         * soh.total_shipping_cost, soh.total_amount, soh.bill_to_address_id, soh.contact_phone, 
         * soh.pay_date, soh.status, soh.creation_date, sol.sales_line_id, sol.product_id, 
         * sol.product_type, sol.product_description, sol.product_detail, sol.ordered_quantity, 
         * sol.unit_selling_price, sol.unit_final_price, sol.line_order
         * FROM sc_sales_order_hdr soh
         * INNER JOIN sc_sales_order_line sol ON sol.sales_order_id = soh.sales_order_id
         * WHERE soh.ship_to_address_id = 182 
         * ORDER BY soh.sales_order_id, sol.line_order DESC
         */
        $select = $this->select()
            ->setIntegrityCheck( false )
            ->from( 
                array( 'soh' => $this->_name ), 
                array( 'soh.sales_order_id, soh.customer_id, soh.shipping_method_id, soh.order_shipping_cost,soh.total_shipping_cost, soh.total_amount, soh.bill_to_address_id, soh.contact_phone,soh.pay_date, soh.status, soh.last_modification' ))
            ->join( 
                array( 'sol' => 'SC_SALES_ORDER_LINE' ), 'sol.sales_order_id = soh.sales_order_id' ,
                array( 'sol.sales_line_id, sol.product_id,sol.product_type, sol.product_description, sol.product_detail, sol.ordered_quantity,sol.unit_selling_price, sol.unit_final_price, sol.line_order' ))
            ->where( 'soh.bill_to_address_id = ?', $idBranche )
            ->order( 'soh.sales_order_id DESC')
            ->order( 'sol.line_order DESC' );
        return ($query == false ) ? $this->fetchRow( $select ) : $select;
    }    

    /**
     * Devuelve un array con todos los datos de la orden
     *
     * @param mixed StdClass
     */
    public function getOrderData( $orderId )
    {
        /**
         *   SELECT *
         *   FROM SC_SALES_ORDER_HDR soh 
         *   INNER JOIN CM_CUSTOMER  cus ON cus.customer_id = soh.customer_id
         *   INNER JOIN SC_PAYMENT_METHODS  paym ON paym.payment_method_id = soh.payment_method_id
         *   INNER JOIN SC_ADDRESS  ship ON ship.address_id = soh.ship_to_address_id
         *   INNER JOIN SC_ZONE szcity ON szcity.zone_id = ship.zone_id
         * 
         *   INNER JOIN SC_ZONE szcounty ON szcounty.zone_id = szcity.parent_zone_id
         *   INNER JOIN SC_ZONE  szstate ON szstate.zone_id = szcounty.parent_zone_id
         *   INNER JOIN SC_ZONE szcountry ON szcountry.zone_id = szstate.parent_zone_id 
         *   INNER JOIN SC_ADDRESS  bill ON bill.address_id = soh.bill_to_address_id
         *   INNER JOIN SC_ZONE  bzcity ON bzcity.zone_id = bill.zone_id
         * 
         *   INNER JOIN SC_ZONE  bzcounty ON bzcounty.zone_id = bzcity.parent_zone_id
         *   INNER JOIN SC_ZONE  bzstate ON bzstate.zone_id = bzcounty.parent_zone_id
         *   INNER JOIN SC_ZONE bzcountry ON bzcountry.zone_id = bzstate.parent_zone_id 
         *   WHERE soh.sales_order_id = $idOrder
         */
        $query = $this->select()
            ->setIntegrityCheck( false )
            ->from( 
                array( 'soh' => 'sc_sales_order_hdr' ), 
                array( 'soh.customer_id customerId, soh.pay_date, soh.status,  soh.shipping_method_id, soh.order_shipping_cost, soh.total_shipping_cost, soh.total_amount, soh.last_modification, soh.contact_phone' ))
        /*    ->join( 
                array( 'cus' => 'cm_customer' ), 'cus.customer_id = soh.customer_id' ,
                array( 'cus.customer_id, cus.customer_code, cus.customer_type, customer_name' ))
        */
            ->joinLeft( 
                array( 'paym' => 'sc_payment_method' ), 
                'paym.payment_method_id = soh.payment_method_id' ,
                array( 'paym.ticket, paym.description, paym.payment_method_id' ))
            ->join( 
                array( 'ship' => 'sc_address' ), 'ship.address_id = soh.ship_to_address_id' ,
                array( 'ship.address_id saddress_id, ship.address1, ship.address2, ship.street_name sstreet_name, ship.street_number sstreet_number, ship.apt_floor sapt_floor, ship.zip_code szip_code' ))
			->join(
				array( 'scship' => 'sc_shipping_method' ), 'scship.shipping_method_id = soh.shipping_method_id', 
				array( 'scship.method shipping_method_id, scship.priority' )) 
            ->join( 
                array( 'szcity' => 'sc_zone' ), 'szcity.zone_id = ship.zone_id' ,
                array( 'szcity.name szcityn' ))
            ->join( 
                array( 'szcounty' => 'sc_zone' ), 'szcounty.zone_id = szcity.parent_zone_id',
                array( 'szcounty.name szcountyn' ))
            ->join( 
                array( 'szstate' => 'sc_zone' ), 'szstate.zone_id = szcounty.parent_zone_id',
                array( 'szstate.name szstaten' ))
            ->join( 
                array( 'szcountry' => 'sc_zone' ), 'szcountry.zone_id = szstate.parent_zone_id',
                array( 'szcountry.name szcountryn' ))
            ->join( 
                array( 'bill' => 'sc_address' ), 'bill.address_id = soh.bill_to_address_id',
                array( 'bill.address_id baddress_id, bill.address1, bill.address2, bill.street_name bstreet_name, bill.street_number bstreet_number, bill.apt_floor bapt_floor, bill.zip_code bzip_code' ))
            ->join( 
                array( 'bzcity' => 'sc_zone' ), 'bzcity.zone_id = bill.zone_id',
                array( 'bzcity.name bzcityn' ))
            ->join( 
                array( 'bzcounty' => 'sc_zone' ), 'bzcounty.zone_id = bzcity.parent_zone_id',
                array( 'bzcounty.name bzcountyn' ))
            ->join( 
                array( 'bzstate' => 'sc_zone' ), 'bzstate.zone_id = bzcounty.parent_zone_id',
                array( 'bzstate.name bzstaten' ))
            ->join( 
                array( 'bzcountry' => 'sc_zone' ), 'bzcountry.zone_id = bzstate.parent_zone_id',
                array( 'bzcountry.name bzcountryn' ))
            ->where( 'soh.sales_order_id = ?', $orderId 
		);

        return $this->fetchAll( $query );
    }
    
    

    public function getHistory( $idCustomer )
    {
		if (isset($idCustomer) && $idCustomer > 0) {
			$select = $this->select( 'soh.*')
            	->setIntegrityCheck( false )
                ->from( array( 'soh' => 'SC_SALES_ORDER_HDR' ))
                ->where( 'soh.customer_id = ?', $idCustomer )
                ->where( 'soh.status = ?', 'C' )
                ->order( 'soh.pay_date DESC' );
			return $this->fetchAll( $select );
		} else {
        	return false;
		}
    }
}